/*
 * Copyright (C) 2012-2013 Michael L.R. Marques
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA  02110-1301, USA.
 * 
 * Contact: michaellrmarques@gmail.com
 */
package com.jm.jmsql.objects;

import com.jm.jmsql.objects.base.JMSqlObject;
import com.jm.commons.logging.LogSystem;
import com.jm.commons.utils.Constants;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.List;
import java.util.regex.Pattern;
import javax.swing.Icon;
import javax.swing.ImageIcon;

/**
 *
 * @author Michael L.R. Marques
 */
public class Table extends JMSqlObject implements Cloneable {
    
    // The Table Description
    private String description;
    
    /**
     * 
     * @param name 
     */
    public Table(String name) {
        this(null, name);
    }
    
    /**
     * 
     * @param parent 
     */
    public Table(JMSqlObject parent)  {
        this(parent, "");
    }
    
    /**
     * 
     * @param parent
     * @param name 
     */
    public Table(JMSqlObject parent, String name) {
        this(parent, name, "", new ArrayList(0));
    }
    
    /**
     * 
     * @param parent
     * @param name
     * @param description 
     */
    public Table(JMSqlObject parent, String name, String description) {
        this(parent, name, description, new ArrayList(0));
    }
    
    /**
     * 
     * @param parent
     * @param name 
     * @param children 
     */
    public Table(JMSqlObject parent, String name, List<JMSqlObject> children) {
        this(parent, name, "", children);
    }
    
    /**
     * 
     * @param parent
     * @param name
     * @param description
     * @param children 
     */
    public Table(JMSqlObject parent, String name, String description, List<JMSqlObject> children) {
        super(parent, name);
        this.description = description;
        addAll(children);
    }
    
    /**
     * Not needed
     */
    @Override
    public void load() {
        if (isLoaded()) {
            return;
        }
        if (isEmpty()) {
            JMSqlObject object = getParent().getParent();
            DatabaseMetaData dmd = ((Definition) (object instanceof Definition ? object : object.getParent())).getDatabaseMetaData();
            try {
                try (ResultSet results = dmd.getColumns(object instanceof Catalog ? object.getName() : null, object instanceof Schema ? object.getName() : null, getName(), "%")) {
                    int column = 0;
                    int columns = results.getMetaData().getColumnCount();
                    while (results.next()) {
                        try {
                            if (columns >= 12 &&
                                    columns <= 17) {
                                add(new Column(this, results.getString(4), results.getInt(5), results.getString(6), results.getInt(7), results.getInt(8), results.getString(12), ++column));
                            } else if (columns >= 18 &&
                                            columns <= 22) {
                                add(new Column(this, results.getString(4), results.getInt(5), results.getString(6), results.getInt(7), results.getInt(8), results.getString(12), ++column, results.getString(18).equals("YES")));
                            } else if (columns == 23) {
                                add(new Column(this, results.getString(4), results.getInt(5), results.getString(6), results.getInt(7), results.getInt(8), results.getString(12), ++column, results.getString(18).equals("YES"), results.getString(23).equals("YES")));
                            } else {
                                add(new Column(this, results.getString(4), results.getInt(5), results.getString(6), results.getInt(7), results.getInt(8), results.getString(12), ++column, results.getString(18).equals("YES"), results.getString(23).equals("YES"), results.getString(24).equals("YES")));
                            }
                        } catch (SQLException sqle) {
                            LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Add a column to the table", sqle);
                        }
                    }
                }
                try (ResultSet results = dmd.getPrimaryKeys(object instanceof Catalog ? object.getName() : null, object instanceof Schema ? object.getName() : null, getName())) {
                    while (results.next()) {
                        for (JMSqlObject columnObject : this) {
                            Column column = (Column) columnObject;
                            if (column.getName().equals(results.getString(4))) {
                                column.setPrimaryKey(true);
                            }
                        }
                    }
                }
            } catch (SQLException sqle) {
                LogSystem.log(getClass(), getClass().getEnclosingMethod(), "Building the list of columns from the database meta data", sqle);
            }
        }
        super.load();
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public Icon getIcon() {
        return new ImageIcon(getClass().getClassLoader().getResource("com/jm/jmsql/icons/table.png"));
    }
    
    /**
     * 
     * @return 
     */
    public String getDescription() {
        return this.description;
    }
    
    /**
     * 
     * @param description 
     */
    public void setDescription(String description) {
        this.description = description;
    }
    
    /**
     * 
     * @return 
     */
    @Override public JMSqlObject.Type getObjectType() {
        return JMSqlObject.Type.TABLE;
    }
    
    /**
     * 
     * @return 
     */
    public String selectSQLQuery() {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        // Add the columns
        for (JMSqlObject child : this) {
            Column column = (Column) child;
            sql.append(column.getName());
            // If the is the final column, exclude the comma
            if (column.getIndex() <= size() - 1) {
                sql.append(", ");
            }
        }
        sql.append(Constants.NEW_LINE);
        sql.append("FROM ");
        sql.append(hasParent() && getParent().hasParent() &&  getParent().getParent().getName() != null ? getParent().getParent().getName() + "." : "");
        sql.append(getName());
        return sql.toString();
    }
    
    /**
     * 
     * @return 
     */
    public String insertSQLQuery() {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO ");
        if (((Schema) getParent().getParent()).getName() != null) {
            sql.append(((Schema) getParent().getParent()).getName());
            sql.append(".");
        }
        sql.append(getName());
        sql.append(" (");
        // Add the columns
        for (JMSqlObject child : this) {
            // Add the columns name
            sql.append(child.getName());
            // If the is the final column, exclude the comma
            if (((Column) child).getIndex() <= size() - 1) {
                sql.append(", ");
            }
        }
        sql.append(")");
        sql.append(Constants.NEW_LINE);
        sql.append("VALUES (");
        // Add the columns
        for (JMSqlObject child : this) {
            Column column = (Column) child;
            // Set a value with basic type functionality
            if (Number.class.isAssignableFrom(column.getType())) {
                sql.append("0");
            } else if (Date.class.isAssignableFrom(column.getType())) {
                sql.append((new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.sss")).format(GregorianCalendar.getInstance().getTime()));
            } else if (String.class.isAssignableFrom(column.getType())) {
                sql.append("''");
            } else {
                sql.append("null");
            }
            // If the is the final column, exclude the comma
            if (column.getIndex() <= size() - 1) {
                sql.append(", ");
            }
        }
        sql.append(")");
        return sql.toString();
    }
    
    /**
     * 
     * @return 
     */
    public String updateSQLQuery() {
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE ");
        if (((Schema) getParent().getParent()).getName() != null) {
            sql.append(((Schema) getParent().getParent()).getName());
            sql.append(".");
        }
        sql.append(getName());
        return sql.toString();
    }
    
    /**
     * 
     * @return 
     */
    public String deleteSQLQuery() {
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE * FROM ");
        if (((Schema) getParent().getParent()).getName() != null) {
            sql.append(((Schema) getParent().getParent()).getName());
            sql.append(".");
        }
        sql.append(getName());
        return sql.toString();
    }
    
    /**
     * 
     * @return 
     */
    public String dropSQLQuery() {
        StringBuilder sql = new StringBuilder();
        sql.append("DROP TABLE ");
        if (((Schema) getParent().getParent()).getName() != null) {
            sql.append(((Schema) getParent().getParent()).getName());
            sql.append(".");
        }
        sql.append(getName());
        return sql.toString();
    }
    
    /**
     * 
     * @return 
     */
    public String createSQLQuery() {
        // Return a blank string if no parent exists
        if (!hasParent()) {
            return "";
        }
        // Get the parent
        Schema schema = (Schema) getParent().getParent();
        // Build the SQL structure string
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE ");
        sql.append(getObjectType().toString().toUpperCase());
        sql.append(Constants.SPACE);
        sql.append(schema.getName() != null ? schema.getName() + "." : "");
        sql.append(getName());
        sql.append(" (");
        sql.append(Constants.NEW_LINE);
        // Add the columns
        for (JMSqlObject child : this) {
            Column column = (Column) child;
            sql.append(Constants.TAB);
            sql.append(column.getName());
            sql.append(Constants.SPACE);
            sql.append(column.getTypeName());
            if (!Date.class.isAssignableFrom(column.getType())) {
                sql.append("(");
                sql.append(column.getLength());
                if (!Integer.class.equals(column.getType()) &&
                        Number.class.isAssignableFrom(column.getType())) {
                    sql.append(",");
                    sql.append(column.getDecimals());
                }
                sql.append(")");
            }
            // Check if the column is a primary key
            if (column.isPrimaryKey()) {
                sql.append(" PRIMARY KEY");
            }
            // Check if the column is nullable
            if (!column.isNullable()) {
                sql.append(" NOT NULL");
            }
            // Check if the column is auto generated
            if (column.isAutoGenerated()) {
                sql.append(" AUTO");
            }
            // If the is the final column, exclude the comma
            if (column.getIndex() <= size() - 1) {
                sql.append(",");
                sql.append(Constants.NEW_LINE);
            }
        }
        sql.append(Constants.NEW_LINE);
        sql.append(")");
        return sql.toString();
    }
    
    /**
     * 
     * @return 
     */
    @Override public int getColumnCount() {
        return 3;
    }
    
    /**
     * 
     * @param index
     * @return 
     */
    @Override public String getColumnName(int index) {
        switch (index) {
            case 0: return "Name";
            case 1: return "Columns";
            case 2: return "Description";
            default: return null;
        }
    }
    
    /**
     * 
     * @param index
     * @return 
     */
    @Override public Object getColumn(int index) {
        switch (index) {
            case 0: return getName();
            case 1: return size();
            case 2: return this.description == null ? "" : this.description;
            default: return null;
        }
    }
    
    /**
     * 
     * @param index
     * @return 
     */
    @Override
    public boolean isColumnEditable(int index) {
        switch (index) {
            case 0:;
            case 2: return true;
            default: return false;
        }
    }
    
    /**
     * 
     * @param index
     * @param object 
     */
    @Override
    public void setColumn(int index, Object object) {
        if (isInEditMode()) {
            switch (index) {
                case 0: {
                    if (Pattern.matches("[A-Za-z0-9_@]+", object.toString())) {
                        setName(object.toString());
                    }
                    break;
                } case 2: this.description = object.toString();
            }
        }
    }
    
    /**
     * 
     * @return 
     */
    @Override
    public Object clone() {
        return new Table(getParent(), getName(), this.description, this);
    }
    
}
